﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
using Gacn.ControleAlunos.Library.Entities;

namespace Gacn.ControleAlunos.Library.DataAccess
{
    internal static class PresencaDataAccess
    {

        #region Insert Methods
        /// <summary>
        /// Salva uma unica presenca no banco de dados.
        /// A acao e atomica, sendo que seu resultado e referenciado
        /// no valor do boolean que e retornado.
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public static Boolean SalvaPresenca(PresencaEntity presenca){
            //salva uma unica presenca
            return false;
        }

        /// <summary>
        /// Salva uma lista de presencas no banco de dados.
        /// A acao e atomica, sendo que seu resultado e referenciado
        /// no valor do boolean que e retornado.
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public static Boolean SalvaPresenca(List<PresencaEntity> presencas){

            SqlConnection connection = null;
            SqlCommand command = null;

            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = connection.BeginTransaction("Salva Presencas");

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection = connection;
            command.Transaction = transaction;

            try
            {
                foreach(PresencaEntity p in presencas) {


                    if (p.Equals(presencas[0]))
                    {
                        command.CommandText = "uspRegistraPresencaPorIdPessoa";
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add("idPessoa", SqlDbType.Int).Value = p.PessoaID;
                        command.Parameters.Add("DataAula", SqlDbType.DateTime).Value = p.Data;
                        command.Parameters.Add("idInsertUser", SqlDbType.Int).Value = 2;

                        command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
                        command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        command.Parameters["idPessoa"].Value = p.PessoaID;
                        command.Parameters["DataAula"].Value = p.Data;
                    }

                    command.ExecuteNonQuery();
                }

                // Attempt to commit the transaction.
                transaction.Commit();
                Console.WriteLine("Both records are written to database.");
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  Message: {0}", ex.Message);

                // Attempt to roll back the transaction.
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred
                    // on the server that would cause the rollback to fail, such as
                    // a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                    Console.WriteLine("  Message: {0}", ex2.Message);
                }

                return false;
            }
        }
        #endregion

        #region Get Methods
        static DataTable GetAllPresenca()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "SELECT idPessoa, dtHrPresenca FROM [tbPresenca]";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }
        #endregion

        #region Update Methods
        public static bool AtualizaDataDaPresenca(int ID, DateTime data)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspAtualizaDataDaPresenca";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("idPessoa", SqlDbType.Int).Value = ID;
            command.Parameters.Add("idTurma", SqlDbType.DateTime).Value = data;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return (bool)command.Parameters["SUCESSO"].Value;
        }
        #endregion

    }
}
